﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using ZedGraph;
using Nauka.engine;

namespace Nauka.database
{
    public class DBConn
    {
        private MainProgram mp;
        private DBSettings dbs;

        public DBSettings dbSettings
        {
            get
            {
                return dbs;
            }
            set
            {
                dbs = value;
            }
        }

        public DBConn(MainProgram mp)
        {
            this.mp = mp;
            dbs = new DBSettings("localhost", "PokerManagerDB", "apacz", "rybaimiecz");
        }

        public bool tryConnect()
        {
            bool result;
            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();
                mySqlConnection.Close();
                result = true;
            }
            catch (SqlException sqle)
            {
                result = false;
                Console.WriteLine("Rzucam========================================================");
                throw sqle;
            }

            return result;
        }

        public DataTable getPositionStats()
        {
            DataTable dt = new DataTable();

            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
                mySqlCommand.CommandText = "EXEC GET_POSITION_STATS @player_id";
                mySqlCommand.Parameters.Add("@player_id", SqlDbType.BigInt);
                mySqlCommand.Parameters["@player_id"].Value = mp.hero;

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mySqlCommand;
                adapter.Fill(dt);

                mySqlConnection.Close();
            }
            catch (SqlException sqle)
            {
                Console.WriteLine(sqle);
            }

            return dt;
        }

        public void count_player_stats(long player_id){
            DataTable dt = new DataTable();

            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
                mySqlCommand.CommandText = "EXEC COUNT_AF @player_id "
                                          + "EXEC COUNT_AF_FLOP @player_id "
                                          + "EXEC COUNT_AF_TURN @player_id "
                                          + "EXEC COUNT_AF_RIVER @player_id "
                                          + "EXEC COUNT_BB_PER_100 @player_id "
                                          + "EXEC COUNT_BB_PER_HH @player_id "
                                          + "EXEC COUNT_FOLD_HAND_PREFLOP @player_id "
                                          + "EXEC COUNT_FOLD_HAND_FLOP @player_id "
                                          + "EXEC COUNT_FOLD_HAND_TURN @player_id "
                                          + "EXEC COUNT_FOLD_HAND_RIVER @player_id "
                                          + "EXEC COUNT_HANDS @player_id "
                                          + "EXEC COUNT_MINUTES_PLAYED @player_id "
                                          + "EXEC COUNT_PFR @player_id "
                                          + "EXEC COUNT_SAW_FLOP_ALL_HANDS @player_id "
                                          + "EXEC COUNT_VPIP @player_id "
                                          + "EXEC COUNT_VPIP_SB @player_id "
                                          + "EXEC COUNT_WENT_TO_SHOWDOWN @player_id "
                                          + "EXEC COUNT_WENT_TO_SHOWDOWN_WSF @player_id "
                                          + "EXEC COUNT_WINRATE_HH @player_id "
                                          + "EXEC COUNT_WON @player_id "
                                          + "EXEC COUNT_WON_AT_SHOWDOWN @player_id "
                                          + "EXEC COUNT_STEAL_ATTEMPT @player_id "
                                          + "EXEC COUNT_WON_BB @player_id ";
                mySqlCommand.Parameters.Add("@player_id", SqlDbType.NVarChar);
                mySqlCommand.Parameters["@player_id"].Value = player_id;

                mySqlCommand.ExecuteNonQuery();

                mySqlConnection.Close();
            }
            catch (SqlException sqle)
            {
                Console.WriteLine(sqle);
            }
        }

        public long getHeroId(string hero)
        {
            DataTable dt = new DataTable();
            long k = 0;

            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
                mySqlCommand.CommandText = "SELECT player_id FROM Player WHERE player_name = @player_name";
                mySqlCommand.Parameters.Add("@player_name", SqlDbType.NVarChar);
                mySqlCommand.Parameters["@player_name"].Value = hero;

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mySqlCommand;
                adapter.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    k = Convert.ToInt64(dr[0]);
                }

                mySqlConnection.Close();
            }
            catch (SqlException sqle)
            {
                Console.WriteLine(sqle);
            }

            return k;
        }

        public DataTable getStartingHands(string player_name)
        {
            DataTable dt = new DataTable();

            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
                mySqlCommand.CommandText = "SELECT card_1, card_2, won, player_name FROM game_player "
                                            + "INNER JOIN Player ON game_player.player_id = Player.player_id "
                                            + "WHERE player_name = @player_name";
                mySqlCommand.Parameters.Add("@player_name", SqlDbType.NVarChar);
                mySqlCommand.Parameters["@player_name"].Value = player_name;

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mySqlCommand;
                adapter.Fill(dt);

                mySqlConnection.Close();
            }
            catch (SqlException sqle)
            {
                Console.WriteLine(sqle);
            }

            return dt;
        }

        public DataTable getSessions()
        {
            DataTable dt = new DataTable();

            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
                mySqlCommand.CommandText = "EXEC GET_SESSION_DATES @player_id";
                mySqlCommand.Parameters.Add("@player_id", SqlDbType.BigInt);
                mySqlCommand.Parameters["@player_id"].Value = mp.hero;

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mySqlCommand;
                adapter.Fill(dt);

                mySqlConnection.Close();
            }
            catch(SqlException sqle){
                Console.WriteLine(sqle);
            }

            return dt;
        }

        public ArrayList getLimits(){
            ArrayList result = new ArrayList();
            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand sqlcmd = mySqlConnection.CreateCommand();
                sqlcmd.CommandText = "SELECT DISTINCT big_blind, small_blind FROM Game";

                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = sqlcmd;
                adapter.Fill(dt);
                foreach(DataRow dr in dt.Rows){
                    string BB = Convert.ToString(dr[0]);
                    BB = BB.Substring(0, BB.IndexOf(',') + 3);
                    string SB = Convert.ToString(dr[1]);
                    SB = SB.Substring(0, BB.IndexOf(',') + 3);
                    string str = "FL " + SB + " / " + BB;
                    result.Add(str);
                }

                mySqlConnection.Close();
            }
            catch (SqlException sqle)
            {
                Console.WriteLine(sqle);
            }

            return result;
        }

        public PointPairList getPoints(string day, string player_name)
        {
            PointPairList ppl = new PointPairList();
            DataTable dt = new DataTable();

            string[] tablica = day.Split();

            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
                mySqlCommand.CommandText = "SELECT won FROM Player "
                                            + "INNER JOIN game_player ON Player.player_id = game_player.player_id "
                                            + "INNER JOIN Game ON game_player.game_id = Game.game_id "
                                            + "WHERE player_name = @player_name ";

                switch (tablica.GetLength(0))
                {
                    case 1:
                        mySqlCommand.CommandText += "AND YEAR(date_and_time) = @year ";
                        mySqlCommand.Parameters.Add("@year", SqlDbType.Int);
                        mySqlCommand.Parameters["@year"].Value = Convert.ToInt16(tablica[0]);
                        break;
                    case 2:
                        mySqlCommand.CommandText += "AND DATENAME(month, date_and_time) = @month AND YEAR(date_and_time) = @year ";
                        mySqlCommand.Parameters.Add("@month", SqlDbType.NVarChar);
                        mySqlCommand.Parameters["@month"].Value = tablica[0];
                        mySqlCommand.Parameters.Add("@year", SqlDbType.Int);
                        mySqlCommand.Parameters["@year"].Value = Convert.ToInt16(tablica[1]);
                        break;
                    case 3:

                        mySqlCommand.CommandText += "AND DAY(date_and_time) = @day "
                                                 +"AND DATENAME(month, date_and_time) = @month AND YEAR(date_and_time) = @year ";
                        mySqlCommand.Parameters.Add("@day", SqlDbType.Int);
                        mySqlCommand.Parameters["@day"].Value = Convert.ToInt16(tablica[0]);
                        mySqlCommand.Parameters.Add("@month", SqlDbType.NVarChar);
                        mySqlCommand.Parameters["@month"].Value = tablica[1];
                        mySqlCommand.Parameters.Add("@year", SqlDbType.Int);
                        mySqlCommand.Parameters["@year"].Value = Convert.ToInt16(tablica[2]);
                        break;
                }
                mySqlCommand.CommandText += "ORDER BY date_and_time";

                mySqlCommand.Parameters.Add("@player_name", SqlDbType.NVarChar);
                mySqlCommand.Parameters["@player_name"].Value = player_name;
 
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mySqlCommand;
                adapter.Fill(dt);

                double won = 0.0;
                int counter = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    won += (double)Convert.ToDouble(dr[0]);
                    ppl.Add(counter++, won);
                }

                mySqlConnection.Close();
            }
            catch (SqlException sqle)
            {
                Console.WriteLine(sqle);
            }
            return ppl;
        }

        public Hand getHand(long game_id)
        {
            DataTable dt = new DataTable();
            ArrayList actions = new ArrayList();
            ArrayList game_players = new ArrayList();

            Card flop_card_1;
            Card flop_card_2;
            Card flop_card_3;
            Card turn_card;
            Card river_card;

            int number_of_players;
            int button;
            float big_blind;
            float small_blind;
            float pool;
            Hand hand2 = new Hand();

            try
            {
                Hand hand;
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
                mySqlCommand.CommandText = "SELECT * FROM Game WHERE game_id = @game_id";

                mySqlCommand.Parameters.Add("@game_id", SqlDbType.BigInt);
                mySqlCommand.Parameters["@game_id"].Value = game_id;

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mySqlCommand;
                adapter.Fill(dt);

                DataRow dr = dt.Rows[0];
                    flop_card_1 = new Card((int)Convert.ToInt16(dr[4]));
                    flop_card_2 = new Card((int)Convert.ToInt16(dr[5]));
                    flop_card_3 = new Card((int)Convert.ToInt16(dr[6]));
                     turn_card = new Card((int)Convert.ToInt16(dr[7]));
                    river_card = new Card((int)Convert.ToInt16(dr[8]));
                    number_of_players = (int)Convert.ToInt16(dr[11]);
                    big_blind = (float)Convert.ToDouble(dr[12]);
                    small_blind = (float)Convert.ToDouble(dr[13]);
                    pool = (float)Convert.ToDouble(dr[14]);
                    button = (int)Convert.ToInt16(dr[9]);
                
                SqlCommand getPlayers = mySqlConnection.CreateCommand();
                getPlayers.CommandText = "SELECT game_player.*, player_name FROM game_player "
                                           + "INNER JOIN Player ON Player.player_id = game_player.player_id "
                                           + "WHERE game_id = @game_id ";
                getPlayers.Parameters.Add("@game_id", SqlDbType.BigInt);
                getPlayers.Parameters["@game_id"].Value = game_id;

                dt.Clear();
                DataTable t2 = new DataTable();
                SqlDataAdapter adapter2 = new SqlDataAdapter();
                adapter2.SelectCommand = getPlayers;
                adapter2.Fill(t2);
                foreach (DataRow drs in t2.Rows)
                {
                    GamePlayer gp = new GamePlayer((int)Convert.ToInt16(drs[3]), Convert.ToString(drs[12]), (float)Convert.ToDouble(drs[4]), game_id);
                    Console.WriteLine(Convert.ToString(drs[11]));
                    gp.setPosition(Position_string.getPosition(Convert.ToString(drs[11]).Trim()));
                    gp.setWon((float)Convert.ToDouble(drs[10]));
                    if (!(drs[1] is DBNull))
                    {
                        gp.setCard_1(new Card((int)Convert.ToInt16(drs[1])));
                        gp.setCard_2(new Card((int)Convert.ToInt16(drs[2])));
                    }

                    game_players.Add(gp);
                }

                SqlCommand getActions = mySqlConnection.CreateCommand();
                getActions.CommandText = "SELECT player_name, game_action.*, seat_number FROM game_action "
                                        + "INNER JOIN game_player "
                                        + "ON game_player.game_id = game_action.game_id "
                                        + "AND game_player.player_id = game_action.game_player_id "
                                        + "INNER JOIN Player ON game_player.player_id = Player.player_id "
                                        + "WHERE game_action.game_id = @game_id ";
                getActions.Parameters.Add("@game_id", SqlDbType.BigInt);
                getActions.Parameters["@game_id"].Value = game_id;

                dt.Clear();
                DataTable t3 = new DataTable();
                adapter.SelectCommand = getActions;
                adapter.Fill(t3);
                foreach (DataRow drs in t3.Rows)
                {
                    actions.Add(new engine.Action(game_id, Convert.ToInt16(drs[1]), Convert.ToString(drs[2]), Convert.ToInt16(drs[7]), Convert.ToString(drs[3]), (float)Convert.ToDouble(drs[4]), Convert.ToString(drs[0])));
                }

                hand = new Hand(game_id, 0, new DateTime(), flop_card_1, flop_card_2, flop_card_3, turn_card, river_card,
                    button, 0, number_of_players, big_blind, small_blind, pool, game_players, actions);
                    

                mySqlConnection.Close();
                return hand;
            }
            catch (SqlException sqle)
            {
                Console.WriteLine(sqle);
            }
 
            return hand2;
        }


        public DataTable getYearWinnings(string nick_name)
        {
            DataTable dt = new DataTable();
            DataTable ds = new DataTable();
            ds.Columns.Add("Year");
            ds.Columns.Add("Hands");
            ds.Columns.Add("$ overall");
            ds.Columns.Add("BigBlinds");
            ds.Columns.Add("BB/100");

            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

                mySqlCommand.CommandText = "select year(date_and_time), count(game_player.game_id), sum(won), "
                + "sum(won/Game.big_blind) from Player "
                + "inner join game_player on Player.player_id = game_player.player_id "
                + "inner join Game on Game.game_id = game_player.game_id "
                + "where player_name = 'kfiatkos' "
                + "group by year(date_and_time)";

                mySqlCommand.Parameters.Add("@player_name", SqlDbType.NVarChar);
                mySqlCommand.Parameters["@player_name"].Value = nick_name;

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mySqlCommand;
                adapter.Fill(dt);

                foreach (DataRow dr in dt.Rows)
                {
                    object[] row = {dr[0], dr[1], dr[2], dr[3], (Convert.ToDouble(dr[3]))*100/(int)dr[1]};
                    ds.Rows.Add(row);
                }

                mySqlConnection.Close();
            }
            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            return ds;
        }
        public DataTable getMonthlyWinnings(string nick_name)
        {
            DataTable dt = new DataTable();
            DataTable ds = new DataTable();
            ds.Columns.Add("Month");
            ds.Columns.Add("Hands");
            ds.Columns.Add("$ overall");
            ds.Columns.Add("BigBlinds");
            ds.Columns.Add("BB/100");

            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

                mySqlCommand.CommandText = "select datename(month, date_and_time), "
                + "year(date_and_time), count(game_player.game_id), sum(won), "
                + "sum(won/Game.big_blind) from Player "
                + "inner join game_player on Player.player_id = game_player.player_id "
                + "inner join Game on Game.game_id = game_player.game_id "
                + "where player_name = 'kfiatkos' "
                + "group by datename(month, date_and_time), year(date_and_time)";

                mySqlCommand.Parameters.Add("@player_name", SqlDbType.NVarChar);
                mySqlCommand.Parameters["@player_name"].Value = nick_name;

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mySqlCommand;
                adapter.Fill(dt);

                foreach (DataRow dr in dt.Rows)
                {
                    object[] row = {Convert.ToString(dr[0]) + " " + Convert.ToString(dr[1]), dr[2],
                                       dr[3], dr[4], (Convert.ToDouble(dr[4]))*100/(int)dr[2]};
                    ds.Rows.Add(row);
                }

                mySqlConnection.Close();
            }
            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            return ds;
        }

        public DataTable getDailyWinnings(string nick_name)
        {
            DataTable dt = new DataTable();
            DataTable ds = new DataTable();
            ds.Columns.Add("Day");
            ds.Columns.Add("Hands");
            ds.Columns.Add("$ overall");
            ds.Columns.Add("BigBlinds");
            ds.Columns.Add("BB/100");

            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

                mySqlCommand.CommandText = "select day(date_and_time), datename(month, date_and_time), "
                + "year(date_and_time), count(game_player.game_id), sum(won), "
                + "sum(won/Game.big_blind) from Player "
                + "inner join game_player on Player.player_id = game_player.player_id "
                + "inner join Game on Game.game_id = game_player.game_id "
                + "where player_name = 'kfiatkos' "
                + "group by day(date_and_time), datename(month, date_and_time), year(date_and_time)";

                mySqlCommand.Parameters.Add("@player_name", SqlDbType.NVarChar);
                mySqlCommand.Parameters["@player_name"].Value = nick_name;

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mySqlCommand;
                adapter.Fill(dt);
                
                foreach (DataRow dr in dt.Rows)
                {
                    object[] row = {Convert.ToString(dr[0]) + " " + Convert.ToString(dr[1]) + " " + Convert.ToString(dr[2]), dr[3],
                                       dr[4], dr[5], (Convert.ToDouble(dr[5]))*100/(int)dr[3]};
                    ds.Rows.Add(row);
                }

                mySqlConnection.Close();
            }
            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            return ds;
        }

        public DataTable getHtmlPlayerStats(string nick_name)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

                mySqlCommand.CommandText = "SELECT played_hands, vpip, pfr, af, went_to_showdown_wsf FROM Player WHERE player_name = @player_name";
                mySqlCommand.Parameters.Add("@player_name", SqlDbType.NVarChar);
                mySqlCommand.Parameters["@player_name"].Value = nick_name;
                //Console.WriteLine(nick_name);

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mySqlCommand;
                adapter.Fill(dt);

                //foreach (DataRow dr in dt.Rows)
                //{
                //Console.WriteLine((string)dr[0]);
                //}

                mySqlConnection.Close();
            }
            catch (SqlException sqle)
            {
                Console.WriteLine(sqle);
            }


            return dt;
        }

        public DataTable getPlayerStats(string nick_name)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

                mySqlCommand.CommandText = mp.statsFilter.generateSQLquery();
                Console.WriteLine(mySqlCommand.CommandText);
                mySqlCommand.Parameters.Add("@player_name", SqlDbType.NVarChar);
                mySqlCommand.Parameters["@player_name"].Value = nick_name;
                //Console.WriteLine(nick_name);

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mySqlCommand;
                adapter.Fill(dt);

                //foreach (DataRow dr in dt.Rows)
                //{
                    //Console.WriteLine((string)dr[0]);
                //}

                mySqlConnection.Close();
            }
            catch(SqlException sqle)
            {
                Console.WriteLine(sqle);
            }


            return dt;
        }

        public DataTable GetPlayersAll()
        {
            DataTable dt = new DataTable();

            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

                mySqlCommand.CommandText = "SELECT DISTINCT player_name FROM Player";

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mySqlCommand;
                adapter.Fill(dt);

                mySqlConnection.Close();
                
            }
            catch(SqlException)
            {

            }
            return dt;
        }

        public DataTable GetHandHistoryPositions(string pos)
        {
            DataTable ds = new DataTable();
            DataTable data_table_change = new DataTable();
            data_table_change.Columns.Add("Date And Time");
            data_table_change.Columns.Add("Card 1");
            data_table_change.Columns.Add("Card 2");
            data_table_change.Columns.Add("$ won");
            data_table_change.Columns.Add("BB won");
            data_table_change.Columns.Add("Game id");

            try
            {
                
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
                mySqlCommand.CommandText = "SELECT date_and_time as 'Date and Time', card_1 as 'Card 1', card_2 as 'Card 2', "
                    + "won as '$ won', won/Game.big_blind as 'BB won', + Game.game_id as 'Game id' "
                    + "FROM Player INNER JOIN game_player ON Player.player_id = game_player.player_id INNER JOIN Game "
                    + "ON game_player.game_id = Game.game_id WHERE player_name = 'kfiatkos' AND position = @position";

                mySqlCommand.Parameters.Add("@position", SqlDbType.NChar, 5);
                mySqlCommand.Parameters["@position"].Value = pos;

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mySqlCommand;
                adapter.Fill(ds);

                foreach (DataRow dr in ds.Rows)
                {
                    object[] row = {dr[0], Card.stringValue((int)Convert.ToInt16(dr[1])), 
                                    Card.stringValue((int)Convert.ToInt16(dr[2])), dr[3], dr[4], dr[5]};
                    data_table_change.Rows.Add(row);
                }

                mySqlConnection.Close();
            }
            catch (SqlException)
            {

            }

            return data_table_change;
        }

        public DataTable GetHandHistorySessions(DateTime begin, DateTime end)
        {
            DataTable ds = new DataTable();
            DataTable data_table_change = new DataTable();
            data_table_change.Columns.Add("Date And Time");
            data_table_change.Columns.Add("Card 1");
            data_table_change.Columns.Add("Card 2");
            data_table_change.Columns.Add("$ won");
            data_table_change.Columns.Add("BB won");
            data_table_change.Columns.Add("Game id");

            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
                mySqlCommand.CommandText = "select date_and_time as 'Date and Time', card_1 as 'Card 1', card_2 as 'Card 2', "
                    + "won as '$ won', won/Game.big_blind as 'BB won', + Game.game_id as 'Game id' "
                    + "from Player inner join game_player on Player.player_id = game_player.player_id inner join Game "
                    + "on game_player.game_id = Game.game_id where player_name = 'kfiatkos' AND "
                    + "date_and_time BETWEEN @date_1 AND @date_2";
                
                mySqlCommand.Parameters.Add("@date_1", SqlDbType.DateTime);
                mySqlCommand.Parameters.Add("@date_2", SqlDbType.DateTime);

                mySqlCommand.Parameters["@date_1"].Value = begin;
                mySqlCommand.Parameters["@date_2"].Value = end;

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mySqlCommand;
                adapter.Fill(ds);

                foreach (DataRow dr in ds.Rows)
                {
                    object[] row = {dr[0], Card.stringValue((int)Convert.ToInt16(dr[1])), 
                                    Card.stringValue((int)Convert.ToInt16(dr[2])), dr[3], dr[4], dr[5]};
                    data_table_change.Rows.Add(row);
                }

                mySqlConnection.Close();
            }
            catch (SqlException)
            {

            }

            return data_table_change;
        }

        public DataTable GetHandHistoryAll()
        {
            DataTable ds = new DataTable();
            DataTable data_table_change = new DataTable();
            data_table_change.Columns.Add("Date And Time");
            data_table_change.Columns.Add("Card 1");
            data_table_change.Columns.Add("Card 2");
            data_table_change.Columns.Add("$ won");
            data_table_change.Columns.Add("BB won");
            data_table_change.Columns.Add("Game id");

            try
            {
                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
                mySqlCommand.CommandText = "SELECT date_and_time as 'Date and Time', card_1 as 'Card 1', card_2 as 'Card 2', "
                    + "won as '$ won', won/Game.big_blind as 'BB won', + Game.game_id as 'Game id' "
                    + "FROM Player INNER JOIN game_player ON Player.player_id = game_player.player_id INNER JOIN Game "
                    + "ON game_player.game_id = Game.game_id WHERE player_name = 'kfiatkos'";

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = mySqlCommand;
                adapter.Fill(ds);

                foreach (DataRow dr in ds.Rows)
                {
                    object[] row = {dr[0], Card.stringValue((int)Convert.ToInt16(dr[1])), 
                                    Card.stringValue((int)Convert.ToInt16(dr[2])), dr[3], dr[4], dr[5]};
                    data_table_change.Rows.Add(row);
                }

                mySqlConnection.Close();
            }catch(SqlException){

            }

            return data_table_change;
        }

        public void InsertHandToDB(Hand hand)
        {
            try
            {

                SqlConnection mySqlConnection = new SqlConnection(dbs.connection_string);
                mySqlConnection.Open();

                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

                mySqlCommand.CommandText =
                    "INSERT INTO Game (" +
                    "  game_id, table_id, date_and_time, flop_card_1, flop_card_2, flop_card_3, turn_card, river_card, button, number_of_players, big_blind, small_blind, rake, pool" +
                    ") VALUES (" +
                    "  @game_id, @table_id, @date_and_time, @flop_card_1, @flop_card_2, @flop_card_3, @turn_card, @river_card, @button, @number_of_players, @big_blind, @small_blind, @rake, @pool" +
                    ")";
                mySqlCommand.Parameters.Add("@game_id", SqlDbType.BigInt);
                mySqlCommand.Parameters.Add("@table_id", SqlDbType.BigInt);
                mySqlCommand.Parameters.Add("@date_and_time", SqlDbType.DateTime);
                mySqlCommand.Parameters.Add("@flop_card_1", SqlDbType.SmallInt);
                mySqlCommand.Parameters.Add("@flop_card_2", SqlDbType.SmallInt);
                mySqlCommand.Parameters.Add("@flop_card_3", SqlDbType.SmallInt);
                mySqlCommand.Parameters.Add("@turn_card", SqlDbType.SmallInt);
                mySqlCommand.Parameters.Add("@river_card", SqlDbType.SmallInt);
                mySqlCommand.Parameters.Add("@button", SqlDbType.SmallInt);
                mySqlCommand.Parameters.Add("@number_of_players", SqlDbType.SmallInt);
                mySqlCommand.Parameters.Add("@big_blind", SqlDbType.Money);
                mySqlCommand.Parameters.Add("@small_blind", SqlDbType.Money);
                mySqlCommand.Parameters.Add("@rake", SqlDbType.Money);
                mySqlCommand.Parameters.Add("@pool", SqlDbType.Money);

                mySqlCommand.Parameters["@game_id"].Value = hand.getGameNr();
                mySqlCommand.Parameters["@table_id"].Value = hand.getTableID();
                mySqlCommand.Parameters["@date_and_time"].Value = hand.getDate();
                mySqlCommand.Parameters["@flop_card_1"].Value = hand.getCard(1);
                mySqlCommand.Parameters["@flop_card_2"].Value = hand.getCard(2);
                mySqlCommand.Parameters["@flop_card_3"].Value = hand.getCard(3);
                mySqlCommand.Parameters["@turn_card"].Value = hand.getCard(4);
                mySqlCommand.Parameters["@river_card"].Value = hand.getCard(5);
                mySqlCommand.Parameters["@button"].Value = hand.getButton();
                mySqlCommand.Parameters["@number_of_players"].Value = hand.getNumOfPlayers();
                mySqlCommand.Parameters["@small_blind"].Value = hand.getSB();
                mySqlCommand.Parameters["@big_blind"].Value = hand.getBB();
                mySqlCommand.Parameters["@rake"].Value = hand.getRake();
                mySqlCommand.Parameters["@pool"].Value = hand.getPool();

                mySqlCommand.ExecuteNonQuery();

                foreach (GamePlayer gp in hand.getGamePlayers())
                {
                    Console.WriteLine(gp.getNickname());
                    long player_id;
                    SqlCommand sqlCommandCheckPlayer = mySqlConnection.CreateCommand();
                    sqlCommandCheckPlayer.CommandText =
                        "SELECT player_id FROM Player WHERE player_name = @pname";
                    sqlCommandCheckPlayer.Parameters.Add("@pname", SqlDbType.NVarChar);
                    sqlCommandCheckPlayer.Parameters["@pname"].Value = gp.getNickname();
                    SqlDataReader sqlDr = sqlCommandCheckPlayer.ExecuteReader();
                    if (sqlDr.HasRows)
                    {
                        sqlDr.Read();
                        player_id = (long)sqlDr[0];
                        sqlDr.Close();
                    }
                    else
                    {
                        sqlDr.Close();
                        SqlCommand sqlCommNewPlayer = mySqlConnection.CreateCommand();
                        sqlCommNewPlayer.CommandText =
                            "INSERT INTO Player ( player_name ) VALUES ( @player_name )";
                        sqlCommNewPlayer.Parameters.Add("@player_name", SqlDbType.NVarChar);
                        sqlCommNewPlayer.Parameters["@player_name"].Value = gp.getNickname();
                        sqlCommNewPlayer.ExecuteNonQuery();

                        SqlCommand sqlCommandCheckPlayerID = mySqlConnection.CreateCommand();
                        sqlCommandCheckPlayerID.CommandText =
                            "SELECT player_id FROM Player WHERE player_name = @pname";
                        sqlCommandCheckPlayerID.Parameters.Add("@pname", SqlDbType.NVarChar);
                        sqlCommandCheckPlayerID.Parameters["@pname"].Value = gp.getNickname();

                        SqlDataReader sqldatar = sqlCommandCheckPlayerID.ExecuteReader();
                        sqldatar.Read();
                        player_id = (long)sqldatar[0];
                        sqldatar.Close();
                    }


                    SqlCommand mySqlCommandGamePlayers = mySqlConnection.CreateCommand();
                    mySqlCommandGamePlayers.CommandText =
                    "INSERT INTO game_player (" +
                    "  player_id, card_1, card_2, seat_number, amount_$, game_id, sit_out, small_blind, big_blind, dead_blind, won, position" +
                    ") VALUES (" +
                    "  @player_id, @card_1, @card_2, @seat_number, @amount, @game_id, @sit_out, @small_blind, @big_blind, @dead_blind, @won, @position" +
                    ")";

                    mySqlCommandGamePlayers.Parameters.Add("@player_id", SqlDbType.BigInt);
                    mySqlCommandGamePlayers.Parameters.Add("@card_1", SqlDbType.SmallInt);
                    mySqlCommandGamePlayers.Parameters.Add("@card_2", SqlDbType.SmallInt);
                    mySqlCommandGamePlayers.Parameters.Add("@seat_number", SqlDbType.SmallInt);
                    mySqlCommandGamePlayers.Parameters.Add("@amount", SqlDbType.Float);
                    mySqlCommandGamePlayers.Parameters.Add("@game_id", SqlDbType.BigInt);
                    mySqlCommandGamePlayers.Parameters.Add("@sit_out", SqlDbType.Bit);
                    mySqlCommandGamePlayers.Parameters.Add("@big_blind", SqlDbType.Bit);
                    mySqlCommandGamePlayers.Parameters.Add("@small_blind", SqlDbType.Bit);
                    mySqlCommandGamePlayers.Parameters.Add("@dead_blind", SqlDbType.Bit);
                    mySqlCommandGamePlayers.Parameters.Add("@won", SqlDbType.Money);
                    mySqlCommandGamePlayers.Parameters.Add("@position", SqlDbType.NChar);


                    mySqlCommandGamePlayers.Parameters["@player_id"].Value = player_id;
                    if (gp.getCard(1) == null)
                        mySqlCommandGamePlayers.Parameters["@card_1"].Value = DBNull.Value;//gp.getCard(1).intValue();
                    else
                        mySqlCommandGamePlayers.Parameters["@card_1"].Value = gp.getCard(1).intValue();
                    if (gp.getCard(2) == null)
                        mySqlCommandGamePlayers.Parameters["@card_2"].Value = DBNull.Value;//gp.getCard(2).intValue();
                    else
                        mySqlCommandGamePlayers.Parameters["@card_2"].Value = gp.getCard(2).intValue();
                    mySqlCommandGamePlayers.Parameters["@seat_number"].Value = gp.getSeatNr();
                    mySqlCommandGamePlayers.Parameters["@amount"].Value = gp.getAmount();
                    mySqlCommandGamePlayers.Parameters["@game_id"].Value = gp.getGameNr();
                    mySqlCommandGamePlayers.Parameters["@sit_out"].Value = gp.getSO();
                    mySqlCommandGamePlayers.Parameters["@big_blind"].Value = gp.getBB();
                    mySqlCommandGamePlayers.Parameters["@small_blind"].Value = gp.getSB();
                    mySqlCommandGamePlayers.Parameters["@dead_blind"].Value = gp.getDB();
                    mySqlCommandGamePlayers.Parameters["@won"].Value = gp.getWon();
                    mySqlCommandGamePlayers.Parameters["@position"].Value = Position_string.getString(gp.getPosition());

                    mySqlCommandGamePlayers.ExecuteNonQuery();

                }

                foreach (engine.Action action in hand.getActions())
                {
                    SqlCommand mySqlCommandActions = mySqlConnection.CreateCommand();
                    mySqlCommandActions.CommandText =
                    "INSERT INTO game_action (" +
                    "  lp, round, action, amount, game_id, game_player_id" +
                    ") VALUES (" +
                    "  @lp, @round, @action, @amount, @game_id, @game_player_id" +
                    ")";

                    mySqlCommandActions.Parameters.Add("@game_id", SqlDbType.BigInt);
                    mySqlCommandActions.Parameters.Add("@lp", SqlDbType.Int);
                    mySqlCommandActions.Parameters.Add("@round", SqlDbType.NChar);
                    mySqlCommandActions.Parameters.Add("@action", SqlDbType.NChar);
                    mySqlCommandActions.Parameters.Add("@amount", SqlDbType.Float);
                    mySqlCommandActions.Parameters.Add("@game_player_id", SqlDbType.BigInt);

                    mySqlCommandActions.Parameters["@game_id"].Value = hand.getGameNr();
                    mySqlCommandActions.Parameters["@lp"].Value = action.getLp();
                    mySqlCommandActions.Parameters["@round"].Value = action.getRound();
                    mySqlCommandActions.Parameters["@action"].Value = action.getAction();
                    mySqlCommandActions.Parameters["@amount"].Value = action.getAmount();

                        long player_id;

                        SqlCommand sqlCommandCheckPlayerID = mySqlConnection.CreateCommand();
                        sqlCommandCheckPlayerID.CommandText =
                            "SELECT player_id FROM Player WHERE player_name = @pname";
                        sqlCommandCheckPlayerID.Parameters.Add("@pname", SqlDbType.NVarChar);
                        sqlCommandCheckPlayerID.Parameters["@pname"].Value = action.getPlayerNN();
                        
                        SqlDataReader sqldatar = sqlCommandCheckPlayerID.ExecuteReader();
                        sqldatar.Read();
                        player_id = (long)sqldatar[0];
                        sqldatar.Close();

                    mySqlCommandActions.Parameters["@game_player_id"].Value = player_id;

                    mySqlCommandActions.ExecuteNonQuery();

                }


                Console.WriteLine("Successfully added hand " + hand.getGameNr());

                mySqlConnection.Close();
            }
            catch (SqlException sqle)
            {
                Console.WriteLine("Hand nr " + hand.getGameNr() + " is already in DB...");// +  sqle);
                throw sqle;
            }
        }
    }
}
